import numpy as np
import pymysql
import pandas as pd

class Mysqlutils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            db='scenic',
            port=3306,
            charset='utf8'
        ) 

    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """SELECT t.tourist_agency_name, rel.id_no LEFT(rel.id_no, 2)as province_code, DAYOFWEEK(gate.create_time) as non_weekend, cast(substring(rel.id_no,7,4) as unsigned) as birth_day FROM ticket_order_user_rel LEFT JOIN ticket_order t on t.id = rel.order_id Left JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id WHERE t.pay_time != '' and t.tourist_agency_name != ''"""
        cursor.excute(sql)
        ret = cursor.fetchall()

        df = pd.DataFrame(ret)
        df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)
        df['valid_id'] = df['valid_id'].apply(lambda x: 1 if x and str(x).split != ''else 0)
        df['out_province_ratio'] = df.apply(
            lambda x:1 if(x['valid_id'] and x['province_code'] != 44) else 0 if x['valid_id'] else np.nan,
            axis = 1
        )
        df['elderly_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 44) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors = ('id_no', 'count'),
            valid_visitors = ('valid_id', 'sum'),
            out_province_ratio = ('out_province_ratio', 'sum'),
            elderly_ratio = ('elderly', 'sum'),
            non_weekend = ('non_weekend', 'mean')

        )
        result.to_csv('scenic_data')

if __name__ == '__main__':
    mu = Mysqlutils()
    mu.get_scenic_data()